/*
 * Created on 10.06.2004
 */

package de.localsport.util;

import java.sql.Connection;
import java.sql.Statement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

/**
 * Updates the bet scores according to the match
 * results.
 * 
 * @author MaS
 *
 */

public class ScoreUpdator {

	private Connection conn = null;

	public ScoreUpdator ()
		throws java.sql.SQLException {
			
		this.openConnection();
	}

	public ScoreUpdator (Connection conn) {
		this.conn = conn;
	}

	public int updateMatchScores (int match_id)
		throws java.sql.SQLException {

		int flag = -1;

		String sSQL2 = "SELECT b.bet_id, b.home_guess, b.guest_guess, m.home_score, m.guest_score, m.match_type " +
			   "FROM bets b, matches m " +
			   "WHERE b.match_id = " + match_id + " " +
			   "AND b.match_id = m.match_id " +
			   "AND b.home_guess IS NOT NULL " +
			   "AND b.guest_guess IS NOT NULL " +
			   "AND m.home_score IS NOT NULL " +
			   "AND m.guest_score IS NOT NULL";

		Statement stat = conn.createStatement();
		ResultSet rs = stat.executeQuery(sSQL2);
		
		ArrayList al = new ArrayList();
		ScoreCalculator mb = null;
		while (rs.next()) {
			mb = new ScoreCalculator ();
			mb.setBet_id (rs.getInt(1));
			mb.setMatch_id (match_id);
			mb.setHome_guess (rs.getInt(2));
			mb.setGuest_guess (rs.getInt(3));
			mb.setHome_score (rs.getInt(4));
			mb.setGuest_score (rs.getInt(5));
			mb.setMatch_type (rs.getInt(6));
			mb.calcScore();
			al.add(mb);
		}
		// Update scores
		PreparedStatement ps = null;
			
		if (!al.isEmpty()) {
			String sSQL3 = "UPDATE bets SET score = ? WHERE bet_id = ?";
			ps = conn.prepareStatement(sSQL3);
			for (int j = 0; j < al.size(); j++) {
				ScoreCalculator mb2 = (ScoreCalculator) al.get(j);
				ps.setInt(1,mb2.getScore());
				ps.setInt(2,mb2.getBet_id());
				flag = flag * ps.executeUpdate();
			}
		} else flag = 1;

		// Tidy up
		try {
			if ( stat != null) stat.close();
			if ( ps != null ) ps.close();
		}
		catch (java.sql.SQLException ignore) {} 

		return flag;
	}

	public int updateBetScores (int match_id)
		throws java.sql.SQLException {

		int flag = -1;

		String sSQL2 = "SELECT b.bet_id, b.home_guess, m.home_score, m.match_type " +
			   "FROM bets b, matches m " +
			   "WHERE b.match_id = " + match_id + " " +
			   "AND b.match_id = m.match_id " +
			   "AND b.home_guess IS NOT NULL " +
			   "AND m.home_score IS NOT NULL ";
			   
		Statement stat = conn.createStatement();
		ResultSet rs = stat.executeQuery(sSQL2);
		
		ArrayList al = new ArrayList();
		ScoreCalculator mb = null;
		while (rs.next()) {
			mb = new ScoreCalculator ();
			mb.setBet_id (rs.getInt(1));
			mb.setHome_guess (rs.getInt(2));
			mb.setHome_score (rs.getInt(3));
			mb.setMatch_type (rs.getInt(4));
			mb.setMatch_id (match_id);
			mb.calcScore();
			al.add(mb);
		}
	
		// Update scores
		PreparedStatement ps = null;
			
		if (!al.isEmpty()) {
			String sSQL3 = "UPDATE bets SET score = ? WHERE bet_id = ?";
			ps = conn.prepareStatement(sSQL3);
			for (int j = 0; j < al.size(); j++) {
				ScoreCalculator mb2 = (ScoreCalculator) al.get(j);
				ps.setInt(1,mb2.getScore());
				ps.setInt(2,mb2.getBet_id());
				flag = flag * ps.executeUpdate();
			}
		} else flag = 1;

		// Tidy up
		try {
			if ( stat != null) stat.close();
			if ( ps != null ) ps.close();
		}
		catch (java.sql.SQLException ignore) {} 

		return flag;
	}

	public void openConnection ()
		throws java.sql.SQLException {
		
		this.conn = MySqlConnector.cn();
	}

	public void closeConnection () {
			
		try {if (conn != null) this.conn.close();}
		catch (java.sql.SQLException e) {}
	}

	public static void main(String[] args)
		throws java.sql.SQLException {
		
		ScoreUpdator su = new ScoreUpdator();
		su.updateBetScores(3);
		su.closeConnection();		
	}
}
